package com.project.dao.impl;

import com.project.bean.ClassBean;
import com.project.bean.CutPageBean;
import com.project.bean.RoomBean;
import com.project.bean.StudentBean;
import com.project.dao.IStudentDao;
import org.springframework.stereotype.Repository;

import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
@Repository
public class StudentDaoImpl extends BaseDao implements IStudentDao {
    @Override
    public CutPageBean<StudentBean> findByItem(Integer pageNo, String name, String className, String room) {
        CutPageBean<StudentBean> cut = new CutPageBean<>();
        this.setConnection();
        String sql = "SELECT s.*, c.`t_className`,r.`t_roomName` FROM t_student s LEFT JOIN t_class c " +
                "ON s.`fk_classId`=c.`pk_classId` LEFT JOIN t_room r ON s.`fk_roomId`=r.`pk_roomId` WHERE 1=1 ";
        String dynaSql = "";
        if (name != null && name.length() != 0){
            dynaSql += "AND s.`t_studentName` LIKE '%"+name+"%' ";
        }
        if (className!= null && className.length() != 0){
            dynaSql += "AND c.`t_className` LIKE '%"+className+"%' ";
        }
        if (room != null){
            dynaSql += "AND r.`t_roomName`  LIKE '%"+room+"%' ";
        }
        try {
            ps = con.prepareStatement(sql+dynaSql+" LIMIT ?,?");
            ps.setObject(1,(pageNo-1)*CutPageBean.PAGESIZE);
            ps.setObject(2,CutPageBean.PAGESIZE);
            rs = ps.executeQuery();
            while (rs.next()){
                cut.getList().add(this.getStudent());
            }
            ps = con.prepareStatement("SELECT count(*) c FROM t_student s LEFT JOIN t_class c " +
                    "ON s.`fk_classId`=c.`pk_classId` LEFT JOIN t_room r ON s.`fk_roomId`=r.`pk_roomId` WHERE 1=1 "+dynaSql);
            rs = ps.executeQuery();
            while (rs.next()){
                cut.setCount(rs.getInt("c"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
        return cut;
    }

    @Override
    public StudentBean findById(Integer id) {
       this.setConnection();
       StudentBean student = null;
        try {
            ps = con.prepareStatement("SELECT s.*, c.`t_className`,r.`t_roomName` FROM t_student s LEFT JOIN t_class c " +
                    " ON s.`fk_classId`=c.`pk_classId` LEFT JOIN t_room r ON s.`fk_roomId`=r.`pk_roomId` WHERE s.`pk_studentId`=? ");
            ps.setObject(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                student = this.getStudent();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
        return student;
    }

    @Override
    public List<StudentBean> findByRoomId(Integer id) {
        List<StudentBean> list = new ArrayList<>();
        this.setConnection();
        try {
            ps = con.prepareStatement("SELECT s.*, c.`t_className`,r.`t_roomName` FROM t_student s LEFT JOIN t_class c " +
                    " ON s.`fk_classId`=c.`pk_classId` LEFT JOIN t_room r ON s.`fk_roomId`=r.`pk_roomId` WHERE s.`fk_roomId`=? ");
            ps.setObject(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                list.add(this.getStudent());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
        return list;
    }

    @Override
    public void updateRoom(Integer id, Integer roomId) {
       this.setConnection();
        try {
            ps = con.prepareStatement("update t_student set fk_roomId=? where pk_studentId=?");
            ps.setObject(1,roomId);
            ps.setObject(2,id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
    }

    @Override
    public void del(Integer id) {
        this.setConnection();
        try {
            ps = con.prepareStatement("delete from t_student where pk_studentId=?");
            ps.setObject(1,id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
    }

    @Override
    public void add(StudentBean student) {
        this.setConnection();
        try {
            ps = con.prepareStatement("insert into t_student(t_studentName,t_studentPhone,t_studentSex,t_studentImg,fk_classId,fk_roomId,t_studentDate) values (?,?,?,?,?,?,?)");
            ps.setObject(1,student.getStudentName());
            ps.setObject(2,student.getStudentPhone());
            ps.setObject(3,student.getSex());
            ps.setObject(4,student.getStudentImg());
            ps.setObject(5,student.getClassBean().getId());
            ps.setObject(6,student.getRoomBean().getId());
            ps.setObject(7,student.getStudentDate());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
    }

    @Override
    public Integer countByClass(Integer classId) {
        this.setConnection();
        Integer num = 0;
        try {
            ps = con.prepareStatement("select count(*)num from t_student where fk_classId = ?");
            ps.setObject(1,classId);
            rs = ps.executeQuery();
            if (rs.next()){
                num = rs.getInt("num");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
        return num;
    }

    @Override
    public Integer countByRoom(Integer roomId) {
        this.setConnection();
        Integer num = 0;
        try {
            ps = con.prepareStatement("select count(*)num from t_student where fk_roomId = ?");
            ps.setObject(1,roomId);
            rs = ps.executeQuery();
            if (rs.next()){
                num = rs.getInt("num");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
        return num;
    }

    public StudentBean getStudent(){
        StudentBean student = new StudentBean();
        try {
            student.setId(rs.getInt("pk_studentId"));
            student.setStudentName(rs.getString("t_studentName"));
            student.setStudentPhone(rs.getString("t_studentPhone"));
            String bir = rs.getString("t_studentDate");
            student.setStudentDate(bir!=null? LocalDate.parse(bir):null);
            student.setSex(rs.getString("t_studentSex"));
            student.setStudentImg(rs.getString("t_studentImg"));
            ClassBean classBean = new ClassBean();
            classBean.setClassName(rs.getString("t_className"));
            student.setClassBean(classBean);
            RoomBean room = new RoomBean();
            room.setRoomName(rs.getString("t_roomName"));
            student.setRoomBean(room);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return student;
    }

//    public static void main(String[] args) {
//        IStudentDao dao = new StudentDaoImpl();
////        System.out.println(dao.findByItem(1,"小","j187","双"));
////        System.out.println(dao.findById(1));
////        System.out.println(dao.findByRoomId(1));
////        dao.updateRoom(4,4);
////        dao.add(new StudentBean("王王1","18402888571","男","1.jpg",1,2));
////        dao.del(16);
////        System.out.println(dao.countByClass(1));
//        System.out.println(dao.countByRoom(1));
//    }
}
